<?php

/**
 * @author  spring
 * @version 2014-6-7 上午10:46:49
 */
class  Pandian extends Basic
{

    /**
     * Description 查询条件控制
     * @param                       field_type  bare_field_name
     * @param Kohana_Database_Query $pre
     * @param array                 $condition  查询条件
     * @param string                $flag       为true就执行排序
     * @param String                $tablename  表名
     * @return Kohana_Database_Query
     */
    public static function getListInfoCondition($pre,$condition,$flag = TRUE,$tablename)
    {

        if(!empty($condition['storage_position1'])&&!empty($condition['storage_position2'])){
            $condition1 = array(
                'storage_position',
                '>=',
                trim($condition['storage_position1'])*10000
            );
            $pre        = self::condition($pre,$condition1);
            $condition1 = array(
                'storage_position',
                '<=',
                (trim($condition['storage_position2'])+1)*10000
            );
            $pre        = self::condition($pre,$condition1);
        }

        if(!empty($condition['storage_position1'])&&empty($condition['storage_position2'])){
            $condition1 = array(
                    'storage_position',
                    'like',
                    '%'.trim($condition['storage_position1'])
                ).'%';
            $pre        = self::condition($pre,$condition1);
        }

        if(empty($condition['storage_position1'])&&!empty($condition['storage_position2'])){
            $condition1 = array(
                    'storage_position',
                    'like',
                    '%'.trim($condition['storage_position2'])
                ).'%';
            $pre        = self::condition($pre,$condition1);
        }

        if(!empty($condition['storage'])){
            $condition1 = array(
                'storage_position',
                'like',
                trim($condition['storage']).'%'
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['sku'])&&!empty($condition['sku'])){
            $condition1 = array(
                'sku',
                '=',
                trim($condition['sku'])
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['start_time'])&&!empty($condition['start_time'])){
            $firstday   = date('Y-m-01',strtotime($condition['start_time']));
            $condition1 = array(
                'inventory_date',
                '=',
                $firstday
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['supplier'])&&!empty($condition['supplier'])){
            $condition1 = array(
                'supplier_id',
                '=',
                $condition['supplier']
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['warehouseid'])&&!empty($condition['warehouseid'])){
            $condition1 = array(
                'warehouseid',
                '=',
                $condition['warehouseid']
            );
            $pre        = self::condition($pre,$condition1);
        }
        if(isset($condition['enterprise_dominant'])){
            $condition1 = array(
                'enterprise_dominant',
                '=',
                $condition['enterprise_dominant']
            );
            $pre        = self::condition($pre,$condition1);
        }
        if($flag){
            if(isset($condition['sortcondition'])&&!empty($condition['sortcondition'])){

                switch($condition['sortcondition']){
                    case 1:
                        $value = 'sku';
                        break;
                    case 2:
                        $value = 'supplier_id';
                        break;
                    case 3:
                        $value = 'quantity';
                        break;
                    case 4:
                        $value = 'storage_position';
                        break;
                }
                $sort = $condition['order']==1?'ASC':'DESC';
                $pre  = $pre->order_by($value,$sort);
            }
        }
        return $pre;
    }

    /**
     * Description
     * @param          field_type  bare_field_name
     * @param array    $datas      库存表的数据
     * @param datetime $time
     * @return number 处理库存数据， 加减出入库记录得出结果
     */
    public static function excute_data($datas,$time) {

        for($i = 0;$i<count($datas);$i++){
            $in_nums               = self::getIn($datas[$i],$time);
            $out_num               = self::getOut($datas[$i],$time);
            $datas[$i]['quantity'] = $datas[$i]['quantity'] + $in_nums - $out_num;
            //$lazy                  = self::load_lazy_deliverry($datas[$i]['sku'],date("Y-m-d H:i:s"));
            //$datas[$i]['quantity'] = $datas[$i]['quantity']+$in_nums-$out_num-$lazy;
            //$datas[$i]['lazy']     = $lazy;
        }
        return $datas;
    }

    public static function getActualStock($skus,$time)
    {
        if(empty($skus)){
            return;
        }

        $skus_string = "";
        foreach($skus as $sku){
            $skus_string .= $skus_string?",'".$sku."'":"'".$sku."'";
        }

        $date = date('Y-m-d H:i:s',strtotime($time));

        $sql_string = "select upper(sku) as sku,sum(num) as inventory from (
	SELECT
		warehouseorders.sku AS sku,
		warehouseorders.warehouse_quantity AS num
	FROM
		warehouseorders
	WHERE
		warehouseorders.warehouse_date >= DATE_FORMAT('".$date."', '%Y-%m-01')
	AND
	    warehouseorders.warehouse_date < DATE_FORMAT('".$date."', '%Y-%m-%d')
    UNION ALL
		SELECT
			`deliveryorders`.`sku` AS `sku` ,- (
				`deliveryorders`.`quantity`
			) AS `num`
		FROM
			`deliveryorders`
		WHERE
			`deliveryorders`.`delivery_date` >= DATE_FORMAT('".$date."', '%Y-%m-01')
		AND
		    `deliveryorders`.`delivery_date` < DATE_FORMAT('".$date."', '%Y-%m-%d')

    UNION ALL

		SELECT
			`inventories`.`sku` AS `sku`,
			`inventories`.`quantity` AS `num`
		FROM
			`inventories`
		WHERE
			`inventories`.`inventory_date` = DATE_FORMAT('".$date."', '%Y-%m-01')
	) a where
	sku <> ''
	and sku in (".$skus_string.") GROUP BY sku";

        return DB::query(Database::SELECT,$sql_string)
            ->execute('newerp')
            ->as_array('sku','inventory');
    }

    /**
     * 批量计算库存信息,用于下载信息
     * @time 2014-08-13
     * @param $datas
     * @param $time
     * @return mixed
     */
    public static function excute_data_new($datas,$time)
    {

        /**
         * 不可为空数组
         */
        if(empty($datas)){
            return $datas;
        }

        /**
         * 必须含有sku
         * array(
         * 0=>array(sku=>'sku0'),
         * 1=>array(sku=>'sku123'),
         * )
         */
        if(!isset($datas[0]['sku'])){
            return $datas;
        }

        /**
         * 计算库存信息
         * 得到以sku为键，结果为value的数组，
         * 要求sku不能有重复的
         */
        $skus = Arr::pluck($datas,'sku');
        //$inventory_all = self::get_inventory($skus,$time);
        $inventory            = self::getActualStock($skus,$time);
        $storage_position_all = Skusystemapi::getSkuPlaces($skus);
        $storage_position     = array_combine(Arr::pluck($storage_position_all,'sku'),$storage_position_all);
        unset($storage_position_all);
        //$inventory = array_combine(Arr::pluck($inventory_all,'sku'),$inventory_all);unset($inventory_all);

        /**
         * 处理数据，
         * 添加库存信息等
         */
        for($i = 0;$i<count($datas);$i++){
            //$in_nums=self::getIn($datas[$i],$time);
            //$out_num=self::getOut($datas[$i],$time);
            //$lazy=self::load_lazy_deliverry($datas[$i]['sku'],date("Y-m-d H:i:s"));
            //  		$datas[$i]['quantity']=$datas[$i]['quantity']+$in_nums-$out_num-$lazy;
            //  		$datas[$i]['lazy']=$lazy;

            /**
             * sku必须装换为大写的
             * 数据库不区分大小写，php代码区分
             */
            $skuid = strtoupper($datas[$i]['sku']);//echo "#".$skuid;
            //$datas[$i]['quantity']=isset($inventory[$skuid])?$inventory[$skuid]['inventory']:0;
            $datas[$i]['quantity']         = isset($inventory[$skuid])?$inventory[$skuid]:0;
            $datas[$i]['storage_position'] = isset($storage_position[$skuid]['place'])?$storage_position[$skuid]['place']:'';
            $datas[$i]['lazy']             = 0;
        }

        return $datas;
    }

    //获取老erp扫描已出库，但库存数据还没同步的库存量
    public static function load_lazy_deliverry($sku,$time)
    {
        $start_time = date('Y-m-d H:00:00',strtotime($time));
        $totle      = DB::select(DB::expr('SUM(item_count)AS totle'))
            ->from('erp_orders')
            ->join('erp_orders_products')
            ->on('erp_orders.erp_orders_id','=','erp_orders_products.erp_orders_id')
            ->where('orders_sku','=',$sku)
            ->and_where('orders_out_time','>=',$start_time)
            ->and_where('orders_out_time','<=',$time)
            ->and_where('orders_status','=',5)
            ->execute('readonlyerp')
            ->get('totle',0);
        return $totle;
    }

    /**
     * 查询库存信息，使用一次查询全部的结果
     * @param $skus
     * @param $time
     * @return mixed
     */
    public static function get_inventory($skus,$time)
    {
        if(empty($skus)){
            return;
        }

        $skus_string = "";
        foreach($skus as $sku){
            $skus_string .= $skus_string?",'".$sku."'":"'".$sku."'";
        }

        $date = date('Y-m-d H:i:s',strtotime($time));

        $sql_string = "select upper(sku) as sku,sum(num) as inventory from (
	SELECT
		warehouseorders.sku AS sku,
		warehouseorders.warehouse_quantity AS num
	FROM
		warehouseorders
	WHERE
		warehouseorders.warehouse_date >= DATE_FORMAT('".$date."', '%Y-%m-01')
	AND
	    warehouseorders.warehouse_date < DATE_FORMAT('".$date."', '%Y-%m-%d')
    UNION ALL
		SELECT
			`deliveryorders`.`sku` AS `sku` ,- (
				`deliveryorders`.`quantity`
			) AS `num`
		FROM
			`deliveryorders`
		WHERE
			`deliveryorders`.`delivery_date` >= DATE_FORMAT('".$date."', '%Y-%m-01')
		AND
		    `deliveryorders`.`delivery_date` < DATE_FORMAT('".$date."', '%Y-%m-%d')

    UNION ALL

		SELECT
			`inventories`.`sku` AS `sku`,
			`inventories`.`quantity` AS `num`
		FROM
			`inventories`
		WHERE
			`inventories`.`inventory_date` = DATE_FORMAT('".$date."', '%Y-%m-01')
	) a where
	sku <> ''
	and sku in (".$skus_string.") GROUP BY sku";

        return DB::query(Database::SELECT,$sql_string)
            ->execute('newerp')
            ->as_array();
    }

    /**
     * Description 查询某一时间段入库的总和
     * @param          field_type  bare_field_name
     * @param array    $data
     * @param datetime $time
     * @return int  某一时间段入库数量
     */
    public static function getIn($data,$time)
    {
        $time   = date('Y-m-d',strtotime("+1 day",strtotime($time)));
        $in_num = DB::select(DB::expr('SUM(warehouseorders.warehouse_quantity) as in_num'))
            ->from('warehouseorders')
            ->where('sku','=',$data['sku'])
            ->where('warehouseid', '=', $data['warehosueid'])
            ->where('enterprise_dominant', 'IN', array($data['enterprise_dominant'], 0))
            ->and_where('warehouse_date','>=',$data['inventory_date'])
            ->and_where('warehouse_date','<',$time)
            ->execute('newerp')
            ->get('in_num',0);
        return $in_num;
    }

    /**
     * Description 查询某一时间段出库的总和
     * @param          field_type  bare_field_name
     * @param array    $data
     * @param datetime $time
     * @return int  某一时间段出库数量
     */
    public static function getOut($data,$time)
    {
        $time    = date('Y-m-d',strtotime("+1 day",strtotime($time)));
        $out_num = DB::select(DB::expr('SUM(deliveryorders.quantity) as out_num'))
            ->from('deliveryorders')
            ->where('sku','=',$data['sku'])
            ->where('warehouseid', '=', $data['warehosueid'])
            ->where('enterprise_dominant', 'IN', array($data['enterprise_dominant'], 0))
            ->and_where('delivery_date','>=',$data['inventory_date'])
            ->and_where('delivery_date','<',$time)
            ->execute('newerp')
            ->get('out_num',0);
        return $out_num;
    }

    /**
     * Description 验证盘点上传的execl是否准确
     * @param       field_type  bare_field_name
     * @param array $data       excel表格转化为二维数组
     */
    public static function check_pandian_upload($data)
    {
        $num = count($data);
        for($i = 2;$i<=$num;$i++){
            $sku      = trim($data[$i]['A']);
            $pd_time  = trim($data[$i]['F']);
            $quantity = trim($data[$i]['G']);
            if($i>2){
                if($sku==trim($data[$i-1]['A'])){
                    self::alert('你输入的第'.($i-1).'行和第'.$i.'行重复');
                }
            }
            if(!isset($sku)||empty($sku)){
                self::alert('你输入的第'.$i.'行SKU不能为空');
            }
            if(!isset($pd_time)||empty($pd_time)){
                self::alert('你输入的第'.$i.'行盘点时间');
            }
            if(!isset($pd_time)||empty($pd_time)){
                self::alert('你输入的第'.$i.'行盘点时间');
            }
            if(!isset($quantity)){
                self::alert('你输入的第'.$i.'行盘点量不能为空');
            }
            if(!preg_match('/^([1-2][0-9]{3})[\/|\-](0?[1-9]|10|11|12)[\/|\-]([1-2]?[0-9]|0[1-9]|30|31)$/',$pd_time)){
                self::alert('你输入的第'.$i.'行日期不符合规则');
            }
            if(!preg_match('/^(\d+)?$/',$quantity)){
                self::alert('你输入的第'.$i.'行数量不符合规则');
            }
            if(!Deliverycheck::check_sku($sku)){
                self::alert('你输入的第'.$i.'行SKU有误');
            }
        }
    }

    /**
     * Description   表格上传数据
     * @param       field_type  bare_field_name
     * @param array $data       excel表格转化为二维数组
     * @return array 返回影响的行
     */
    public static function do_pandian_upload($data,$time)
    {
        $count   = count($data);
        $user_id = User::active_user()->id;
        $lines   = array();
        for($i = 2;$i<=$count;$i++){
            $sku           = trim($data[$i]['A']);
            $download_time = trim($data[$i]['B']);
            $upload_time   = date('Y-m-d H:i:s');
            $quantity      = trim($data[$i]['G']);
            $quantity2     = trim($data[$i]['E']);
            $pd_time       = trim($data[$i]['F']);
            $tablename     = 'inventories';
            $pre           = Pandian::getListInfoCondition(Pandian::getListPre($tablename),array(
                'sku'        => $sku,
                'start_time' => date('Y-m-d')
            ),TRUE,$tablename);
            $datas         = Pandian::getList(1,0,$pre);
            $datas         = Pandian::excute_data($datas,date('Y-m-d H:i:s'));
            $quantity3     = $datas[0]['quantity'];
            $num           = $quantity-$quantity2;//实际盘点量-下载表格时的库存量
            //$status 3:盘点数量相等，1：盘亏，2：盘赢
            $status = 3;
            if($num>0){
                $status = 2;
            }elseif($num<0){
                $status = 1;
            }
            $info       = Basic::load_sku_info1($sku);
            $condition1 = array(
                'sku'           => $sku,
                'quantity'      => $quantity,
                'quantity2'     => $quantity2,
                'quantity3'     => $quantity3,
                'pd_time'       => $pd_time,
                'op_time'       => $time,
                'download_time' => $download_time,
                'upload_time'   => $upload_time,
                'user_id'       => $user_id,
                'line'          => $i,
                'num'           => $num,
                'status'        => $status
            );
            $condition1 = array_merge($condition1,$info);
            $insert_id  = self::insert_pandian($condition1);
            if(!$insert_id){
                $lines[] = $i;
            }else{
                $condition = array(
                    'sku'           => $sku,
                    'delivery_date' => $pd_time,
                    'quantity'      => abs($num),
                    'order_id'      => 0
                );
                $condition = array_merge($condition,$info);
                //获取系统默认单价
                $supplier = new Service_Common();
                $result   = json_encode($supplier->get_sku_price($sku));
                if($num>0){
                    //盘赢自动入库
                    $condition['single_price'] = $result;
                    $condition['remark']       = '盘赢自动入库，盘点单id：'.$insert_id;
                    $condition['type']         = 30;
                    Warehouse::insert_warehouse($condition);
                }else{
                    if($num<0){
                        //盘亏自动出库
                        $condition['single_price']       = 0;
                        $condition['remark']             = '盘亏自动出库，盘点单id：'.$insert_id;
                        $condition['type']               = 20;
                        $condition['orders_products_id'] = 0;
                        Delivery::insert_delivery1($condition);
                    }
                }
            }
        }
        return $lines;
    }

    /**
     * Description 网盘点表插入数据
     * @param       field_type  bare_field_name
     * @param array $condition
     * @return int 影响行数
     */
    public static function insert_pandian($condition)
    {
        $warehouseid = Kohana::$config->load('warehouse')->warehouse_id;
        try{
            list($insert_id,$total_rows) = DB::insert('pandians',array(
                'id',
                'warehouseid',
                'sku',
                'sku_name',
                'storage_position',
                'sku_standard',
                'quantity',
                'quantity2',
                'quantity3',
                'pd_time',
                'op_time',
                'download_time',
                'upload_time',
                'user_id',
                'status',
                'num'
            ))
                ->values(array(
                    rand(-9999999999,0),
                    $warehouseid,
                    $condition['sku'],
                    $condition['sku_name'],
                    $condition['storage_position'],
                    $condition['sku_standard'],
                    $condition['quantity'],
                    $condition['quantity2'],
                    $condition['quantity3'],
                    $condition['pd_time'],
                    $condition['op_time'],
                    $condition['download_time'],
                    $condition['upload_time'],
                    $condition['user_id'],
                    $condition['status'],
                    $condition['num']
                ))
                ->execute('newerp');
            return $insert_id;
        }catch(Exception $e){
            self::alert('你输入的SKU:'.$condition['sku'].'当天已经插入,请从第 '.$condition['line'].'行起重新插入');
        }
    }


    public static function  getpandiantpyeByid($id)
    {
        switch($id){
            case 3:
                return '库存无出入';
                break;
            case 1:
                return '盘亏';
                break;
            case 2:
                return '盘赢';
                break;
        }
    }

}
